USE [csc640_fall2011_team3]
GO

/**This will return all foreign keys from table**/
--SELECT
--   'ALTER TABLE ' + OBJECT_NAME(parent_object_id) +  
--    ' DROP CONSTRAINT ' + name
--FROM sys.foreign_keys
--WHERE referenced_object_id IN (object_id('Users'), object_id('Question'), object_id('QuestionAnswer'), object_id('QuestionCategory'), object_id('QuestionQuestionOptionSpecial'), object_id('QuestionOption'))

/***Drop Constraints**/
IF OBJECT_ID('QuestionOptionSpecial') IS NOT NULL ALTER TABLE QuestionOptionSpecial DROP CONSTRAINT FK_QuestionOptionSpecial_QuestionOption
IF OBJECT_ID('Question') IS NOT NULL ALTER TABLE Question DROP CONSTRAINT FK_Question_QuestionCategory
IF OBJECT_ID('QuestionAnswer') IS NOT NULL ALTER TABLE QuestionAnswer DROP CONSTRAINT FK_QuestionAnswer_Question
IF OBJECT_ID('QuestionAnswer') IS NOT NULL ALTER TABLE QuestionAnswer DROP CONSTRAINT FK_QuestionAnswer_Users
IF OBJECT_ID('QuestionOption') IS NOT NULL ALTER TABLE QuestionOption DROP CONSTRAINT FK_QuestionOption_Question


/*** Drop tables **/
IF OBJECT_ID('QuestionOptionSpecial') IS NOT NULL DROP TABLE QuestionOptionSpecial
IF OBJECT_ID('Question') IS NOT NULL DROP TABLE Question
IF OBJECT_ID('QuestionOption') IS NOT NULL DROP TABLE QuestionOption
IF OBJECT_ID('QuestionAnswer') IS NOT NULL DROP TABLE QuestionAnswer
IF OBJECT_ID('QuestionCategory') IS NOT NULL DROP TABLE QuestionCategory
IF OBJECT_ID('Users') IS NOT NULL DROP TABLE Users

/***Create User table ***/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Users](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[Email] [nvarchar](50) NOT NULL,
	[Password] [nvarchar](50) NOT NULL,
	[Firstname] [nvarchar](50) NOT NULL,
	[Lastname] [nvarchar](50) NOT NULL,
	[BirthDate] [date] NOT NULL,
	[Gender] [nvarchar](1) NOT NULL,
	[Ethnicity] [nvarchar](50) NOT NULL,
	[StreetAddress] [nvarchar](50) NULL,
	[City] [nvarchar](50) NULL,
	[State] [nvarchar](50) NULL,
	[ZipCode] [nvarchar](50) NULL,
	[MobilePhone] [nvarchar](50) NULL,
	[Country] [nvarchar](50) NULL,
	[TextFlag] [nvarchar](1) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_Country]  DEFAULT ('US') FOR [Country]
GO

ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_TextFlag]  DEFAULT ('N') FOR [TextFlag]
GO


/***Create QuestionCategory ***/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[QuestionCategory](
	[CategoryID] [int] IDENTITY(1,1) NOT NULL,
	[CategoryDescription] [varchar](50) NOT NULL,
 CONSTRAINT [PK_QuestionCategory] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


/***Create Question***/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Question](
	[QuestionID] [int] IDENTITY(1,1) NOT NULL,
	[QuestionCategoryID] [int] NOT NULL,
	[QuestionSortOrder] [int] NOT NULL,
	[DisplayType] [varchar](150) NOT NULL,
	[FieldName] [varchar](max) NULL,
	[FieldLabel] [varchar](max) NOT NULL,
	[FieldCSS] [varchar](max) NULL,
	[FieldSuffix] [varchar](max) NULL,
	[FieldHelpText] [varchar](150) NULL,
	[FieldHelpURL] [varchar](150) NULL,
	[QuestionActive] [bit] NOT NULL,
 CONSTRAINT [PK_Questions] PRIMARY KEY CLUSTERED 
(
	[QuestionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Question]  WITH CHECK ADD  CONSTRAINT [FK_Question_QuestionCategory] FOREIGN KEY([QuestionCategoryID])
REFERENCES [dbo].[QuestionCategory] ([CategoryID])
GO

ALTER TABLE [dbo].[Question] CHECK CONSTRAINT [FK_Question_QuestionCategory]
GO

/***Create QuestionAnswer **/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[QuestionAnswer](
	[AnswerID] [int] IDENTITY(1,1) NOT NULL,
	[QuestionID] [int] NOT NULL,
	[UserID] [int] NOT NULL,
	[FieldName] [varchar](max) NOT NULL,
	[FieldValue] [varchar](max) NOT NULL,
 CONSTRAINT [PK_QuestionAnswer] PRIMARY KEY CLUSTERED 
(
	[AnswerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[QuestionAnswer]  WITH CHECK ADD  CONSTRAINT [FK_QuestionAnswer_Question] FOREIGN KEY([QuestionID])
REFERENCES [dbo].[Question] ([QuestionID])
GO

ALTER TABLE [dbo].[QuestionAnswer] CHECK CONSTRAINT [FK_QuestionAnswer_Question]
GO

ALTER TABLE [dbo].[QuestionAnswer]  WITH CHECK ADD  CONSTRAINT [FK_QuestionAnswer_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserId])
GO

ALTER TABLE [dbo].[QuestionAnswer] CHECK CONSTRAINT [FK_QuestionAnswer_Users]
GO


/*** Create QuestionOption **/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[QuestionOption](
	[OptionID] [int] IDENTITY(1,1) NOT NULL,
	[QuestionID] [int] NOT NULL,
	[OptionName] [varchar](max) NOT NULL,
	[OptionSortOrder] [int] NOT NULL,
	[OptionLabel] [varchar](max) NOT NULL,
	[OptionActive] [bit] NOT NULL,
 CONSTRAINT [PK_QuestionOption] PRIMARY KEY CLUSTERED 
(
	[OptionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[QuestionOption]  WITH CHECK ADD  CONSTRAINT [FK_QuestionOption_Question] FOREIGN KEY([QuestionID])
REFERENCES [dbo].[Question] ([QuestionID])
GO

ALTER TABLE [dbo].[QuestionOption] CHECK CONSTRAINT [FK_QuestionOption_Question]
GO


/*** Create QuestionOptionSpecial ***/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[QuestionOptionSpecial](
	[SpecialID] [int] IDENTITY(1,1) NOT NULL,
	[OptionID] [int] NOT NULL,
	[SpecialName] [varchar](max) NOT NULL,
	[SpecialDisplayType] [varchar](max) NOT NULL,
 CONSTRAINT [PK_QuestionOptionSpecial_1] PRIMARY KEY CLUSTERED 
(
	[SpecialID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[QuestionOptionSpecial]  WITH CHECK ADD  CONSTRAINT [FK_QuestionOptionSpecial_QuestionOption] FOREIGN KEY([OptionID])
REFERENCES [dbo].[QuestionOption] ([OptionID])
GO

ALTER TABLE [dbo].[QuestionOptionSpecial] CHECK CONSTRAINT [FK_QuestionOptionSpecial_QuestionOption]
GO





